package com.nuoniu.sibanyun.utils;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.nuoniu.sibanyun.common.mapper.JsonMapper;
import com.nuoniu.sibanyun.entity.dto.CrmExcelDto;
import com.nuoniu.sibanyun.entity.dto.DynamicConditionDto;
import com.nuoniu.sibanyun.entity.dto.DynamicDto;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

	/**
	 * 下载模版数据
	 */
	public static void export(Object[] title,  String fileName, String[] data, HttpServletResponse response) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet
			Sheet sheet = wb.createSheet(fileName);
			// 第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
			// 设置默认列宽
			sheet.setDefaultColumnWidth(18);
			Row row = null;
			// 第四步，创建单元格样式：
			Font font = wb.createFont();
			font.setFontName("宋体");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 标题样式
			// 设置自定义颜色
			HSSFPalette customPalette = wb.getCustomPalette();
			customPalette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, (byte) 128, (byte) 198, (byte) 135);
			CellStyle titleStyle = wb.createCellStyle();
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			titleStyle.setFont(font);
			titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置前景色128,198,135
			titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			titleStyle.setBorderBottom((short) 1);
			titleStyle.setBorderLeft((short) 1);
			titleStyle.setBorderRight((short) 1);
			titleStyle.setBorderTop((short) 1);
			// 居中样式
			CellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setBorderBottom((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);
			style.setBorderTop((short) 1);
			// 第五步，创建表头单元格，并设置样式
			Cell cell;
			row = sheet.createRow(0);// 设置日期标题
			row.setHeightInPoints(35);// 设置行高

			for (int i = 0; i < title.length; i++) {
				cell = row.createCell(i);
				cell.setCellValue(title[i] != null ? title[i].toString() : "");
				cell.setCellStyle(titleStyle);
			}

			row = sheet.createRow(1);
			for (int i = 0; i < data.length; i++) {
				cell = row.createCell(i);
				cell.setCellValue(data[i]);
			}

			// 第七步，将文件存到流中
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
			wb.write(response.getOutputStream());
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 下载客户和联系人数据
	 */
	public static void exportCompany(DynamicDto dynamicDto, List<CrmExcelDto> crmExcelDtoList, String fileName, HttpServletResponse response) {
		List<DynamicConditionDto> filedList = dynamicDto.getDtos();
        String companyType = dynamicDto.getCompanyType();
        String[] linkFieldName = {"联系人.姓名", "联系人.性别", "联系人.部门", "联系人.职务", "联系人.手机号码", "联系人.办公电话", "联系人.电子邮件"};
		String[] linkFieldCode = {"name", "gender", "dept", "position", "mobile", "office_phone", "email"};

		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet
			Sheet sheet = wb.createSheet("客户资料");
			// 第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
			// 设置默认列宽
			sheet.setDefaultColumnWidth(18);
			Row row = null;
			// 第四步，创建单元格样式：
			Font font = wb.createFont();
			font.setFontName("宋体");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 标题样式
			// 设置自定义颜色
			HSSFPalette customPalette = wb.getCustomPalette();
			customPalette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, (byte) 128, (byte) 198, (byte) 135);
			CellStyle titleStyle = wb.createCellStyle();
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			titleStyle.setFont(font);
			titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置前景色128,198,135
			titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			titleStyle.setBorderBottom((short) 1);
			titleStyle.setBorderLeft((short) 1);
			titleStyle.setBorderRight((short) 1);
			titleStyle.setBorderTop((short) 1);
			// 居中样式
			CellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setBorderBottom((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);
			style.setBorderTop((short) 1);
			//第五步，创建表头单元格，并设置样式
			Cell cell = null;
			row = sheet.createRow(0);// 设置日期标题
			row.setHeightInPoints(35);// 设置行高

			int i=0;
			for (; i<filedList.size(); i++) { //创建表头
				cell = row.createCell(i);
				cell.setCellValue(filedList.get(i).getName());
				cell.setCellStyle(titleStyle);
			}
			if(companyType.equals("isCustomer")){
                //联系表头
                for(int f=0; f<linkFieldName.length; f++) {
                    cell = row.createCell(i);
                    cell.setCellValue(linkFieldName[f]);
                    cell.setCellStyle(titleStyle);
                    i ++;
                }
            }


			List<Map<String, Object>> list = JsonMapper.fromJsonForMapList(JsonMapper.toJson(crmExcelDtoList));
			int r = 1;
			for (int c = 0; c < list.size(); c++) {
				List<Map> linkmans = crmExcelDtoList.get(c).getLinkMans();
				if(companyType.equals("isCustomer") && linkmans.size() > 0) {
					for(int l=0; l<linkmans.size(); l++) {
						row = sheet.createRow(r);
						row.setHeightInPoints(25);// 设置行高
						Map<String, Object> map = list.get(c);
						setCompanyData(row, cell, filedList, map, style);//客户数据
						Map linkMap = linkmans.get(l);
						for(int link=0; link<linkFieldCode.length; link++) { //添加联系人数据
							String val = linkMap.get(linkFieldCode[link]) + "";
							if (StringUtils.isBlank(val) || val.equals("null")) {
								val = "";
							}
							cell = row.createCell(filedList.size() + link);
							cell.setCellValue(val);
							cell.setCellStyle(style);
						}
						r ++;
					}
				}else {
					row = sheet.createRow(r);
					row.setHeightInPoints(25);// 设置行高
					Map<String, Object> map = list.get(c);
					setCompanyData(row, cell, filedList, map, style);//客户数据
					r ++;
				}
			}

			// 第七步，将文件存到流中
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
			wb.write(response.getOutputStream());
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void setCompanyData(Row row, Cell cell, List<DynamicConditionDto> filedList, Map<String, Object> map, CellStyle style) {
		int i = 0;
		for (DynamicConditionDto filed : filedList) {
			cell = row.createCell(i);
			String val = "";
			//如果是自定义字段
			if(filed.getDataType() == 2) {
				Gson gson = new Gson();
				Map<String,Object> arrList = gson.fromJson(map.get("defineFields") + "", new TypeToken<Map<String, Object>>() {}.getType());
				if(arrList != null && arrList.size() > 0) {
					if(arrList.get(filed.getId().toString()) != null) {
						val = arrList.get(filed.getId().toString()).toString();
					}
				}else {
					val = "";
				}
			}else {
				if(filed.getType().equals("搜索选择")) {
					filed.setCode(filed.getCode().replace("Id", "Name"));
				}
				if(map.get(filed.getCode()) == null) {
					val = "";
				}else {
					val = map.get(filed.getCode()).toString();
				}
			}

			if ("null".equals(val) || val == null) {
				val = "";
			}
			cell.setCellValue(val);
			cell.setCellStyle(style);
			i ++;
		}
	}

	/**
	 * 下载失败原因和数据
	 * @param failData
	 * @param failCause
	 */
	public static void downloadFailCause(String[] excelField, List<List> failData, String[] failCause, HttpServletResponse response) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet
		Sheet sheet = wb.createSheet("导入失败的数据");
		// 第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
		// 设置默认列宽
		sheet.setDefaultColumnWidth(18);
		Row row = null;
		// 第四步，创建单元格样式：
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 标题样式
		// 设置自定义颜色
		HSSFPalette customPalette = wb.getCustomPalette();
		customPalette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, (byte) 128, (byte) 198, (byte) 135);
		CellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleStyle.setFont(font);
		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置前景色128,198,135
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setBorderBottom((short) 1);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setBorderTop((short) 1);
		// 居中样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderBottom((short) 1);
		style.setBorderLeft((short) 1);
		style.setBorderRight((short) 1);
		style.setBorderTop((short) 1);
		// 第五步，创建表头单元格，并设置样式
		Cell cell;
		row = sheet.createRow(0);// 设置日期标题
		row.setHeightInPoints(25);// 设置行高

		/*设置数据单元格格式*/
		CellStyle dataStyle = wb.createCellStyle();
		//dataStyle.setBorderBottom(BorderStyle.THIN);  //设置单元格线条
		dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());   //设置单元格颜色
		//dataStyle.setBorderLeft(BorderStyle.THIN);
		dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		//dataStyle.setBorderRight(BorderStyle.THIN);
		dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		//dataStyle.setBorderTop(BorderStyle.THIN);
		dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		/*dataStyle.setAlignment(HorizontalAlignment.LEFT);    //设置水平对齐方式
		dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐方式*/

		for (int i = 0; i < excelField.length; i++) { //表头
			cell = row.createCell(i);
			cell.setCellValue(excelField[i].replace("\\\"", ""));
			cell.setCellStyle(titleStyle);
		}

		for(int f=0; f<failData.size(); f++) {//填冲内容
			row = sheet.createRow(f + 1);
			List data = failData.get(f);
			for(int i=0; i<excelField.length; i++) {
				if(data.size() - 1 < i) {
					continue;
				}

				cell = row.createCell(i);
				cell.setCellValue(data.get(i).toString());
				cell.setCellStyle(dataStyle);
			}
		}

		Sheet sheet2 = wb.createSheet("导入失败的原因");
		// 设置默认列宽
		sheet2.setDefaultColumnWidth(28);
		Row row2 = null;
		Cell cell2;
		row2 = sheet2.createRow(0);
		row2.setHeightInPoints(25);// 设置行高
		cell2 = row2.createCell(0);
		cell2.setCellValue("失败原因");
		cell2.setCellStyle(titleStyle);
		for(int c=0; c<failCause.length; c++) {
			row2 = sheet2.createRow(c + 1);
			cell2 = row2.createCell(0);
			cell2.setCellValue(failCause[c]);
			cell2.setCellStyle(dataStyle);
		}

		// 第七步，将文件存到流中
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("导入失败的数据和原因.xls", "utf-8"));
		wb.write(response.getOutputStream());
		wb.close();
	}



	/**
	 * 导出数据
	 * @author 小啊刚
	 * @date 2021-06-18
	 */
	public static void exportData(Object[] title,  String fileName, List<HashMap<String, Object>> data, String[] exportFiled, HttpServletResponse response) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet
			Sheet sheet = wb.createSheet(fileName);
			// 第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
			// 设置默认列宽
			sheet.setDefaultColumnWidth(18);
			Row row = null;
			// 第四步，创建单元格样式：
			Font font = wb.createFont();
			font.setFontName("宋体");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 标题样式
			// 设置自定义颜色
			HSSFPalette customPalette = wb.getCustomPalette();
			customPalette.setColorAtIndex(HSSFColor.LIGHT_GREEN.index, (byte) 128, (byte) 198, (byte) 135);
			CellStyle titleStyle = wb.createCellStyle();
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			titleStyle.setFont(font);
			titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置前景色128,198,135
			titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			titleStyle.setBorderBottom((short) 1);
			titleStyle.setBorderLeft((short) 1);
			titleStyle.setBorderRight((short) 1);
			titleStyle.setBorderTop((short) 1);
			// 居中样式
			CellStyle style = wb.createCellStyle();
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setBorderBottom((short) 1);
			style.setBorderLeft((short) 1);
			style.setBorderRight((short) 1);
			style.setBorderTop((short) 1);
			// 第五步，创建表头单元格，并设置样式
			Cell cell;
			row = sheet.createRow(0);// 设置日期标题
			row.setHeightInPoints(35);// 设置行高

			for (int i = 0; i < title.length; i++) {
				cell = row.createCell(i);
				cell.setCellValue(title[i] != null ? title[i].toString() : "");
				cell.setCellStyle(titleStyle);
			}

			for(int r=0; r<data.size(); r++) {
				row = sheet.createRow(r + 1);
				Map<String, Object> mapData = data.get(r);
				for (int i=0; i<exportFiled.length; i++) {
					String value = mapData.get(exportFiled[i]) == null ? "" : mapData.get(exportFiled[i]).toString();
					cell = row.createCell(i);
					cell.setCellValue(value);
				}
			}

			// 第七步，将文件存到流中
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
			wb.write(response.getOutputStream());
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}